--    Program: 		CAPARIMP_CUSTOMER_RELATIONSHIP.sql
--    Description:  Import AR CUSTOMER RELATIONSHIP information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_relationship_id		number;
                   		                 		
   cursor cur_relationship is
      select * from CRST_HZ_RELATIONSHIPS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_relationship loop   	
     l_err_msg_tmp := '';
     
     select HZ_RELATIONSHIPS_S.NEXTVAL 
     INTO l_relationship_id
     FROM DUAL;   
     
     update CRST_HZ_RELATIONSHIPS
     set    relationship_id_n = l_relationship_id
     where  relationship_id = cur.relationship_id; 
     
     begin
       -- import data
       insert into HZ_RELATIONSHIPS
       (
         RELATIONSHIP_ID            ,
         SUBJECT_ID                 ,
         SUBJECT_TYPE               ,
         SUBJECT_TABLE_NAME         ,
         OBJECT_ID                  ,
         OBJECT_TYPE                ,
         OBJECT_TABLE_NAME          ,
         PARTY_ID                   ,
         RELATIONSHIP_CODE          ,
         DIRECTIONAL_FLAG           ,
         COMMENTS                   ,
         START_DATE                 ,
         END_DATE                   ,
         STATUS                     ,
         CREATED_BY                 ,
         CREATION_DATE              ,
         LAST_UPDATED_BY            ,
         LAST_UPDATE_DATE           ,
         LAST_UPDATE_LOGIN          ,
         WH_UPDATE_DATE             ,
         REQUEST_ID                 ,
         PROGRAM_APPLICATION_ID     ,
         PROGRAM_ID                 ,
         PROGRAM_UPDATE_DATE        ,
         ATTRIBUTE_CATEGORY         ,
         ATTRIBUTE1                 ,
         ATTRIBUTE2                 ,
         ATTRIBUTE3                 ,
         ATTRIBUTE4                 ,
         ATTRIBUTE5                 ,
         ATTRIBUTE6                 ,
         ATTRIBUTE7                 ,
         ATTRIBUTE8                 ,
         ATTRIBUTE9                 ,
         ATTRIBUTE10                ,
         ATTRIBUTE11                ,
         ATTRIBUTE12                ,
         ATTRIBUTE13                ,
         ATTRIBUTE14                ,
         ATTRIBUTE15                ,
         ATTRIBUTE16                ,
         ATTRIBUTE17                ,
         ATTRIBUTE18                ,
         ATTRIBUTE19                ,
         ATTRIBUTE20                ,
         GLOBAL_ATTRIBUTE_CATEGORY  ,
         GLOBAL_ATTRIBUTE1          ,
         GLOBAL_ATTRIBUTE2          ,
         GLOBAL_ATTRIBUTE3          ,
         GLOBAL_ATTRIBUTE4          ,
         GLOBAL_ATTRIBUTE5          ,
         GLOBAL_ATTRIBUTE6          ,
         GLOBAL_ATTRIBUTE7          ,
         GLOBAL_ATTRIBUTE8          ,
         GLOBAL_ATTRIBUTE9          ,
         GLOBAL_ATTRIBUTE10         ,
         GLOBAL_ATTRIBUTE11         ,
         GLOBAL_ATTRIBUTE12         ,
         GLOBAL_ATTRIBUTE13         ,
         GLOBAL_ATTRIBUTE14         ,
         GLOBAL_ATTRIBUTE15         ,
         GLOBAL_ATTRIBUTE16         ,
         GLOBAL_ATTRIBUTE17         ,
         GLOBAL_ATTRIBUTE18         ,
         GLOBAL_ATTRIBUTE19         ,
         GLOBAL_ATTRIBUTE20         ,
         CONTENT_SOURCE_TYPE        ,
         RELATIONSHIP_TYPE          ,
         OBJECT_VERSION_NUMBER      ,
         CREATED_BY_MODULE          ,
         APPLICATION_ID             ,
         ADDITIONAL_INFORMATION1    ,
         ADDITIONAL_INFORMATION2    ,
         ADDITIONAL_INFORMATION3    ,
         ADDITIONAL_INFORMATION4    ,
         ADDITIONAL_INFORMATION5    ,
         ADDITIONAL_INFORMATION6    ,
         ADDITIONAL_INFORMATION7    ,
         ADDITIONAL_INFORMATION8    ,
         ADDITIONAL_INFORMATION9    ,
         ADDITIONAL_INFORMATION10   ,
         ADDITIONAL_INFORMATION11   ,
         ADDITIONAL_INFORMATION12   ,
         ADDITIONAL_INFORMATION13   ,
         ADDITIONAL_INFORMATION14   ,
         ADDITIONAL_INFORMATION15   ,
         ADDITIONAL_INFORMATION16   ,
         ADDITIONAL_INFORMATION17   ,
         ADDITIONAL_INFORMATION18   ,
         ADDITIONAL_INFORMATION19   ,
         ADDITIONAL_INFORMATION20   ,
         ADDITIONAL_INFORMATION21   ,
         ADDITIONAL_INFORMATION22   ,
         ADDITIONAL_INFORMATION23   ,
         ADDITIONAL_INFORMATION24   ,
         ADDITIONAL_INFORMATION25   ,
         ADDITIONAL_INFORMATION26   ,
         ADDITIONAL_INFORMATION27   ,
         ADDITIONAL_INFORMATION28   ,
         ADDITIONAL_INFORMATION29   ,
         ADDITIONAL_INFORMATION30   ,
         DIRECTION_CODE             ,
         PERCENTAGE_OWNERSHIP       ,
         ACTUAL_CONTENT_SOURCE                
       )
       values
       (
         l_relationship_id              ,
         CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.SUBJECT_ID),
         cur.SUBJECT_TYPE               ,
         cur.SUBJECT_TABLE_NAME         ,
         CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.OBJECT_ID),
         cur.OBJECT_TYPE                ,
         cur.OBJECT_TABLE_NAME          ,
         CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.PARTY_ID),
         cur.RELATIONSHIP_CODE          ,
         cur.DIRECTIONAL_FLAG           ,
         cur.COMMENTS                   ,
         cur.START_DATE                 ,
         cur.END_DATE                   ,
         cur.STATUS                     ,
         cur.CREATED_BY                 ,
         cur.CREATION_DATE              ,
         cur.LAST_UPDATED_BY            ,
         cur.LAST_UPDATE_DATE           ,
         cur.LAST_UPDATE_LOGIN          ,
         cur.WH_UPDATE_DATE             ,
         cur.REQUEST_ID                 ,
         cur.PROGRAM_APPLICATION_ID     ,
         cur.PROGRAM_ID                 ,
         cur.PROGRAM_UPDATE_DATE        ,
         cur.ATTRIBUTE_CATEGORY         ,
         cur.ATTRIBUTE1                 ,
         cur.ATTRIBUTE2                 ,
         cur.ATTRIBUTE3                 ,
         cur.ATTRIBUTE4                 ,
         cur.ATTRIBUTE5                 ,
         cur.ATTRIBUTE6                 ,
         cur.ATTRIBUTE7                 ,
         cur.ATTRIBUTE8                 ,
         cur.ATTRIBUTE9                 ,
         cur.ATTRIBUTE10                ,
         cur.ATTRIBUTE11                ,
         cur.ATTRIBUTE12                ,
         cur.ATTRIBUTE13                ,
         cur.ATTRIBUTE14                ,
         cur.ATTRIBUTE15                ,
         cur.ATTRIBUTE16                ,
         cur.ATTRIBUTE17                ,
         cur.ATTRIBUTE18                ,
         cur.ATTRIBUTE19                ,
         cur.ATTRIBUTE20                ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY  ,
         cur.GLOBAL_ATTRIBUTE1          ,
         cur.GLOBAL_ATTRIBUTE2          ,
         cur.GLOBAL_ATTRIBUTE3          ,
         cur.GLOBAL_ATTRIBUTE4          ,
         cur.GLOBAL_ATTRIBUTE5          ,
         cur.GLOBAL_ATTRIBUTE6          ,
         cur.GLOBAL_ATTRIBUTE7          ,
         cur.GLOBAL_ATTRIBUTE8          ,
         cur.GLOBAL_ATTRIBUTE9          ,
         cur.GLOBAL_ATTRIBUTE10         ,
         cur.GLOBAL_ATTRIBUTE11         ,
         cur.GLOBAL_ATTRIBUTE12         ,
         cur.GLOBAL_ATTRIBUTE13         ,
         cur.GLOBAL_ATTRIBUTE14         ,
         cur.GLOBAL_ATTRIBUTE15         ,
         cur.GLOBAL_ATTRIBUTE16         ,
         cur.GLOBAL_ATTRIBUTE17         ,
         cur.GLOBAL_ATTRIBUTE18         ,
         cur.GLOBAL_ATTRIBUTE19         ,
         cur.GLOBAL_ATTRIBUTE20         ,
         cur.CONTENT_SOURCE_TYPE        ,
         cur.RELATIONSHIP_TYPE          ,
         cur.OBJECT_VERSION_NUMBER      ,
         cur.CREATED_BY_MODULE          ,
         cur.APPLICATION_ID             ,
         cur.ADDITIONAL_INFORMATION1    ,
         cur.ADDITIONAL_INFORMATION2    ,
         cur.ADDITIONAL_INFORMATION3    ,
         cur.ADDITIONAL_INFORMATION4    ,
         cur.ADDITIONAL_INFORMATION5    ,
         cur.ADDITIONAL_INFORMATION6    ,
         cur.ADDITIONAL_INFORMATION7    ,
         cur.ADDITIONAL_INFORMATION8    ,
         cur.ADDITIONAL_INFORMATION9    ,
         cur.ADDITIONAL_INFORMATION10   ,
         cur.ADDITIONAL_INFORMATION11   ,
         cur.ADDITIONAL_INFORMATION12   ,
         cur.ADDITIONAL_INFORMATION13   ,
         cur.ADDITIONAL_INFORMATION14   ,
         cur.ADDITIONAL_INFORMATION15   ,
         cur.ADDITIONAL_INFORMATION16   ,
         cur.ADDITIONAL_INFORMATION17   ,
         cur.ADDITIONAL_INFORMATION18   ,
         cur.ADDITIONAL_INFORMATION19   ,
         cur.ADDITIONAL_INFORMATION20   ,
         cur.ADDITIONAL_INFORMATION21   ,
         cur.ADDITIONAL_INFORMATION22   ,
         cur.ADDITIONAL_INFORMATION23   ,
         cur.ADDITIONAL_INFORMATION24   ,
         cur.ADDITIONAL_INFORMATION25   ,
         cur.ADDITIONAL_INFORMATION26   ,
         cur.ADDITIONAL_INFORMATION27   ,
         cur.ADDITIONAL_INFORMATION28   ,
         cur.ADDITIONAL_INFORMATION29   ,
         cur.ADDITIONAL_INFORMATION30   ,
         cur.DIRECTION_CODE             ,
         cur.PERCENTAGE_OWNERSHIP       ,
         cur.ACTUAL_CONTENT_SOURCE          
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_RELATIONSHIP', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
